# _*_ coding:utf-8 _*_
# @Time  : 2023.03.02
# @Author: zizlee
import pathlib
import datetime
from fastapi import APIRouter, Header, UploadFile, Form, Query, Body
from pydantic import BaseModel, validator
from collections import Counter
from global_constant import VARIETY
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import file_utils, datetime_utils
from hutool import security
from db import FAConnection
from configs import STATIC_FILE_HOST, FILE_STORAGE
from . import work_utils

chart_explanation_api = APIRouter()


# 管理员查询解说记录
@chart_explanation_api.get('/')
def query_chart_explanation(ruizy_token: str = Header(...), date: int = Query(None),
                            page: int = Query(1), page_size: int = Query(4)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    sql = """
        SELECT w.id,w.user_id,w.title,w.explanation,w.image_path,w.publish_time,w.level_flag,
          u.admin_name
        FROM work_chart_explanation As w
        INNER JOIN ruizy_user As u ON w.user_id=u.id 
        WHERE image_state=1
    """
    prm = []
    if date:
        if not datetime_utils.auth_datetime_string(str(date), f='%Y%m%d'):
            return AllResponse.validate_error(msg='查询参数错误')
        sql += ' AND DATE_FORMAT(publish_time,"%%Y%%m%%d")=%s ORDER BY publish_time DESC;'
        prm.append(date)
    else:
        sql += ' ORDER BY publish_time DESC;'
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['publish_time'] = item['publish_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['image_url'] = STATIC_FILE_HOST + item['image_path']
    return AllResponse.operate_successfully(data=ret)


# 管理员删除解说记录
@chart_explanation_api.delete('/')
def delete_chart_explanation(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    # 先查询删除记录后删除文件
    q_sql = "SELECT id,image_path FROM work_chart_explanation WHERE id=%s LIMIT 1;"
    q_prm = [rid]
    d_sql = "DELETE FROM work_chart_explanation WHERE id=%s LIMIT 1;"
    d_prm = [rid]
    with FAConnection() as cursor:
        cursor.execute(q_sql, q_prm)
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='删除的数据条目不存在.')
        image_file = pathlib.Path(FILE_STORAGE).joinpath(obj['image_path'])
        count = cursor.execute(d_sql, d_prm)
        if count and image_file.exists() and image_file.is_file():
            image_file.unlink()
    return AllResponse.operate_successfully(msg='删除成功，记录减少{}个'.format(count))


class LevelItem(BaseModel):
    rid: int
    level_flag: str

    @validator('level_flag')
    def validate_level_flag(cls, value):
        if value not in ['A', 'B', 'C']:
            raise ValueError('等级只能为A、B、C其中一个')
        return value


# 评级人员查询记录供评级
@chart_explanation_api.get('/level/')
def get_chart_explanation_level(ruizy_token: str = Header(...), date: int = Query(None), level: int = Query(0),
                                page: int = Query(1), page_size: int = Query(4)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT w.id,w.create_time,w.user_id,w.title,w.explanation,w.image_path,w.publish_time,w.level_flag,
          u.admin_name
        FROM work_chart_explanation As w
        INNER JOIN ruizy_user As u ON w.user_id=u.id 
        WHERE image_state=1
    """
    prm = []
    if level:
        sql += " AND level_flag<>''"
    else:
        sql += " AND level_flag=''"
    if date:
        if not datetime_utils.auth_datetime_string(str(date), f='%Y%m%d'):
            return AllResponse.validate_error(msg='查询参数错误')
        sql += ' AND DATE_FORMAT(publish_time,"%%Y%%m%%d")=%s ORDER BY publish_time DESC;'
        prm.append(date)
    else:
        sql += ' ORDER BY publish_time DESC;'
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
        item['publish_time'] = datetime_utils.to_string(item['publish_time'], is_time=False)
        item['image_url'] = STATIC_FILE_HOST + item['image_path']
    return AllResponse.operate_successfully(data=ret)


# 评级人员评级
@chart_explanation_api.put('/level/')
def put_chart_explanation_level(ruizy_token: str = Header(...), row: LevelItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    with FAConnection() as cursor:
        cursor.execute("UPDATE work_chart_explanation SET level_flag=%s WHERE id=%s LIMIT 1;",
                       [row.level_flag, row.rid])
    return AllResponse.operate_successfully(msg='评级成功!')


class ChartExplainItem(BaseModel):
    cid: int = -1
    publish_time: str
    title: str = ''
    image_url: str = ''
    explanation: str = ''

    @validator('publish_time')
    def validate_publish_time(cls, value):
        if not datetime_utils.auth_datetime_string(value):
            raise ValueError('发布日期格式错误，接受`%Y-%m-%d %H:%M:%S`型')
        return value


# 个人创建一个图表解说
@chart_explanation_api.post('/my/')
async def set_my_chart_explanation(ruizy_token: str = Header(...), image: UploadFile = Form(None),
                                   cid: int = Form(-1), publish_time: str = Form(...),
                                   varieties: str = Form(''), title: str = Form(''),
                                   explanation: str = Form('')):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 保存图片文件
    save_path, sql_path = file_utils.get_file_paths(module_name='ImageExplanation', filename=image.filename,
                                                    hashed=True)
    if cid < 0:  # 上传图片呢
        content = await image.read()  # 将文件保存到目标位置
        with open(save_path, "wb") as fp:
            fp.write(content)
        sql = """
            INSERT INTO work_chart_explanation (user_id,title,varieties,explanation,image_path,image_state,publish_time) 
            VALUES (%s,%s,%s,%s,%s,%s,%s);
        """
        prm = [person['uid'], title, '', explanation, sql_path, 0, publish_time]
        # 添加记录
        with FAConnection() as cursor:
            cursor.execute(sql, prm)
            # 本条记录的id
            cursor.execute('SELECT LAST_INSERT_ID() AS insertID;')
            insert_id = cursor.fetchone()['insertID']
        if insert_id < 1:
            # 删除文件
            img = pathlib.Path(save_path)
            if img.exists() and img.is_file():
                img.unlink(missing_ok=True)
            return AllResponse.server_error(msg='服务器错误,保存失败...')
        data = {
            'action': 'image',
            'title': title,
            'image_url': STATIC_FILE_HOST + sql_path,
            'id': insert_id
        }
        return AllResponse.operate_successfully(data=data)
    else:  # 上传文字解说了
        # 检查品种
        variety_list = varieties.split(',')  # 按约定逗号间隔
        variety_list = list(filter(lambda x: x in VARIETY.VARIETY_NAMES.keys(), variety_list))
        if not variety_list:
            return AllResponse.validate_error(msg='关联品种错误.')
        sql = """
            UPDATE work_chart_explanation SET title=%s,varieties=%s,explanation=%s,image_state=1,publish_time=%s 
            WHERE id=%s AND user_id=%s LIMIT 1;
        """
        prm = [title, ','.join(variety_list), explanation, publish_time, cid, person['uid']]
        with FAConnection() as cursor:
            count = cursor.execute(sql, prm)  # 更新内容
        data = {
            'action': 'explain',
            'title': title,
            'explanation': explanation,
            'id': cid
        }
        return AllResponse.operate_successfully(data=data, msg='保存成功，数据数:{}'.format(count))


# 查询个人的解说
@chart_explanation_api.get('/my/')
def get_my_chart_explanation(ruizy_token: str = Header(...), date: int = Query(None),
                             page: int = Query(1), page_size: int = Query(4)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    sql = """
        SELECT id,create_time,user_id,title,explanation,image_path,publish_time
        FROM work_chart_explanation WHERE image_state=1 AND user_id=%s
    """
    prm = [person['uid']]
    if date:
        if not datetime_utils.auth_datetime_string(str(date), f='%Y%m%d'):
            return AllResponse.validate_error(msg='查询参数错误')
        sql += ' AND DATE_FORMAT(publish_time,"%%Y%%m%%d")=%s ORDER BY publish_time DESC;'
        prm.append(date)
    else:
        sql += ' ORDER BY publish_time DESC;'
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
        item['publish_time'] = datetime_utils.to_string(item['publish_time'], is_time=False)
        item['image_url'] = STATIC_FILE_HOST + item['image_path']
    return AllResponse.operate_successfully(data=ret)


# 个人删除一个图表解说
@chart_explanation_api.delete('/my/')
def delete_my_chart_explanation(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 先查询删除记录后删除文件
    q_sql = "SELECT id,image_path,create_time FROM work_chart_explanation WHERE id=%s AND user_id=%s LIMIT 1;"
    q_prm = [rid, person['uid']]
    d_sql = "DELETE FROM work_chart_explanation WHERE id=%s AND user_id=%s LIMIT 1;"
    d_prm = [rid, person['uid']]
    with FAConnection() as cursor:
        cursor.execute(q_sql, q_prm)
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='删除的数据条目不存在.')
        if (datetime.datetime.today() - obj['create_time']).days >= 30:
            return AllResponse.validate_error(msg='较早创建的记录不支持删除了!')
        image_file = pathlib.Path(FILE_STORAGE).joinpath(obj['image_path'])
        count = cursor.execute(d_sql, d_prm)
        if count and image_file.exists() and image_file.is_file():
            image_file.unlink()
    return AllResponse.operate_successfully(msg='删除成功，记录减少{}个'.format(count))


# 图表解说统计
@chart_explanation_api.get('/statistics/')
def statistics_chart_explanation(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                 st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询目标区间的数据
    sql = """
        SELECT a.id,a.publish_time,a.create_time,a.user_id,a.title,a.varieties,a.explanation,a.image_path,a.level_flag,b.admin_name 
        FROM work_chart_explanation As a
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.image_state=1 AND DATE_FORMAT(a.publish_time,"%%Y%%m%%d")>=%s AND DATE_FORMAT(a.publish_time,"%%Y%%m%%d")<=%s
        ORDER BY publish_time DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start_date, end_date))

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))
    # 对记录处理和各人员数据进行处理
    user_dict = {}

    for item in records:
        item['create_time'] = datetime_utils.to_string(item['create_time'], is_time=True)
        item['publish_time'] = datetime_utils.to_string(item['publish_time'], is_time=False)
        item['image_url'] = STATIC_FILE_HOST + item['image_path']
        no_level = 0
        if not item['level_flag']:
            no_level = 1
        if item['user_id'] in user_dict.keys():
            _o = user_dict[item['user_id']]
            _o['data'].append(item)
            _o['count'] += 1
            _o['no_level'] += no_level
        else:
            user_dict[item['user_id']] = {
                'user_id': item['user_id'],
                'admin_name': item['admin_name'],
                'data': [item],
                'count': 1,
                'no_level': no_level
            }
    # 展开数据为列表，并计算评级
    statistics_data = []
    total_levels = []
    total_obj = {'user_id': -1, 'admin_name': '合计', 'data': records, 'count': len(records), 'level_count': '', 'no_level': ''}
    for _, v in user_dict.items():
        # 评级
        levels = [i['level_flag'] for i in v['data'] if i['level_flag']]
        levels.sort()  # 排序ABC
        total_levels.extend(levels)
        v['level_count'] = ''.join(['%d%s' % (v, k) for k, v in Counter(levels).items() if k])
        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x['count'], reverse=True)
    # 加入合计
    if len(statistics_data) > 0:
        total_levels.sort()
        total_obj['level_count'] = ''.join(['%d%s' % (v, k) for k, v in Counter(total_levels).items() if k])
        total_obj['no_level'] = sum([u['no_level'] for u in statistics_data])
        statistics_data.append(total_obj)
    return AllResponse.operate_successfully(data={'statistics': statistics_data, 'statistics_tip': '注：总数量包含未评级的条目。'})
